﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SQLite;
using System.IO;
using System.Windows.Input;
using System.Windows;

namespace KeyboardImageViewer
{
    public static class Database
    {
        private const string ConfigDatabaseFile = "KeyboardImageViewer.sqlite";

        private static Dictionary<string, int> tagsSummary;

        public static string DatabaseFile
        {
            get
            {
#if DEBUG
                return ConfigDatabaseFile;
#else
                string appDataFolder = Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.LocalApplicationData), Utilities.AppDataFolderName);

                if (!Directory.Exists(appDataFolder))
                {
                    Directory.CreateDirectory(appDataFolder);
                }

                return Path.Combine(appDataFolder, ConfigDatabaseFile);
#endif
            }
        }

        public static string ConnectionString
        {
            get
            {
                return "Data Source=" + DatabaseFile;
            }
        }

        #region Table creation
        public static void CreateTables(SQLiteConnection connection)
        {
            CreateTables(connection, true);
        }

        public static void CreateTables(SQLiteConnection connection, bool latestVersion)
        {
            Database.ExecuteNonQuery("CREATE TABLE images (" +
                "id INTEGER PRIMARY KEY AUTOINCREMENT, " +
                "name TEXT, " +
                "tags TEXT, " +
                "dependentTags TEXT, " +
                "rating INTEGER, " +
                "lastWriteTime TEXT, " +
                "included BOOL, " +
                "hash TEXT, " +
                "lastRefreshDate DATETIME)", connection);

            Database.ExecuteNonQuery("CREATE UNIQUE INDEX idIndex ON images (id)", connection);
            Database.ExecuteNonQuery("CREATE INDEX hashIndex ON images (hash)", connection);

            Database.ExecuteNonQuery(
                "CREATE TABLE viewLists (" +
                "id INTEGER PRIMARY KEY AUTOINCREMENT, " +
                "name TEXT, " +
                "listIndex INTEGER, " +
                "orderBy TEXT, " +
                "ascending BOOL, " +
                "rating INTEGER, " +
                "searchString TEXT, " +
                "searchTags BOOL, " +
                "searchFileName BOOL, " +
                "untagged BOOL, " +
                "customClause TEXT)", connection);

            Database.ExecuteNonQuery(
                "CREATE TABLE settings (" +
                "name TEXT, " +
                "value TEXT)", connection);

            Database.ExecuteNonQuery(
                "CREATE TABLE tagDependencies (" +
                "tag TEXT, " +
                "dependentTag TEXT)", connection);

            Database.ExecuteNonQuery(
                "CREATE TABLE importRules (" +
                "rule TEXT)", connection);

            Database.ExecuteNonQuery(
                "CREATE TABLE baseDirectories (" +
                "id INTEGER PRIMARY KEY AUTOINCREMENT, " +
                "path TEXT, " +
                "missing BOOL)", connection);

            Database.ExecuteNonQuery(
                "CREATE TABLE excludedDirectories (" +
                "id INTEGER PRIMARY KEY AUTOINCREMENT, " +
                "baseId INTEGER, " +
                "path TEXT)", connection);

            
            if (latestVersion)
            {
                // All tables V3 and up go here.
                Database.ExecuteNonQuery(
                    "CREATE TABLE inputMapping (" +
                    "inputCode INTEGER, " +
                    "inputType INTEGER, " +
                    "actionCode INTEGER)", connection);

                Database.ExecuteNonQuery(
                    "CREATE TABLE summaries (" +
                    "imageId INTEGER, " +
                    "ineligible BOOL, " +
                    "lastRefreshDate DATETIME, " +
                    "thumbnail BINARY, " +
                    "averageVariance DOUBLE, " +
                    "fractionDrasticPixelBorders DOUBLE, " +
                    "hashPrefix TEXT)", connection);

                Database.ExecuteNonQuery("CREATE UNIQUE INDEX imageIdIndex ON summaries (imageId)", connection);
                Database.ExecuteNonQuery("CREATE INDEX hashPrefixIndex ON summaries (hashPrefix, ineligible)", connection);
            }
            else
            {
                Database.ExecuteNonQuery(
                    "CREATE TABLE summaries (" +
                    "imageId INTEGER, " +
                    "width INTEGER, " +
                    "height INTEGER, " +
                    "lastRefreshDate DATETIME, " +
                    "isCorrupted BOOL, " +
                    "averageVariance DOUBLE, " +
                    "fractionDrasticPixelBorders DOUBLE, " +
                    "thumbnail BINARY)", connection);

                Database.ExecuteNonQuery("CREATE UNIQUE INDEX imageIdIndex ON summaries (imageId)", connection);
            }
        }

        public static void PopulateTablesFromOldConfig(SQLiteConnection connection, ViewerConfig config, string importDirectory)
        {
            using (SQLiteTransaction transaction = connection.BeginTransaction())
            {
                var dependencyInsert = new SQLiteCommand(connection);
                var rulesInsert = new SQLiteCommand(connection);

                try
                {
                    // Add tag dependencies
                    var dependencyTag = new SQLiteParameter();
                    var dependencyDependentTag = new SQLiteParameter();

                    dependencyInsert.CommandText = "INSERT INTO tagDependencies (tag, dependentTag) VALUES (?, ?)";
                    dependencyInsert.Parameters.Add(dependencyTag);
                    dependencyInsert.Parameters.Add(dependencyDependentTag);

                    foreach (KeyValuePair<string, string> pair in config.DependencyList)
                    {
                        dependencyTag.Value = pair.Key;
                        dependencyDependentTag.Value = pair.Value;

                        dependencyInsert.ExecuteNonQuery();
                    }

                    // Add import rules
                    var rulesRule = new SQLiteParameter();

                    rulesInsert.CommandText = "INSERT INTO importRules (rule) VALUES (?)";
                    rulesInsert.Parameters.Add(rulesRule);

                    foreach (string rule in config.ImportRules)
                    {
                        rulesRule.Value = rule;

                        rulesInsert.ExecuteNonQuery();
                    }

                    // Add default view list
                    InsertDefaultViewList(connection);

                    // Add the included directory setting
                    DatabaseUpgrades.MoveSourceDirectory(importDirectory, config.UseSubdirectories, connection);

                    var settingsList = new Dictionary<string, string>();

                    settingsList.Add("Version", "2");
                    settingsList.Add("TrackDuplicates", config.TrackDuplicates.ToString());
                    settingsList.Add("TrackImageDuplicates", config.TrackImageDuplicates.ToString());
                    settingsList.Add("SearchFileName", config.SearchFileName.ToString());
                    settingsList.Add("SearchTags", config.SearchTags.ToString());
                    settingsList.Add("MainWidth", config.MainWidth.ToString());
                    settingsList.Add("MainHeight", config.MainHeight.ToString());
                    settingsList.Add("ImportWidth", config.ImportWidth.ToString());
                    settingsList.Add("ImportHeight", config.ImportHeight.ToString());
                    settingsList.Add("DuplicateWidth", config.DuplicateWidth.ToString());
                    settingsList.Add("DuplicateHeight", config.DuplicateHeight.ToString());
                    settingsList.Add("DeleteDuplicatesOnImport", config.DeleteDuplicatesOnImport.ToString());

                    AddSettingsList(connection, settingsList);

                    transaction.Commit();
                }
                finally
                {
                    if (dependencyInsert != null)
                    {
                        dependencyInsert.Dispose();
                    }

                    if (rulesInsert != null)
                    {
                        rulesInsert.Dispose();
                    }
                }
            }

            using (SQLiteTransaction transaction = connection.BeginTransaction())
            {
                var imageInsert = new SQLiteCommand(connection);

                try
                {
                    var imageId = new SQLiteParameter();
                    var imageName = new SQLiteParameter();
                    var imageTags = new SQLiteParameter();
                    var imageDependentTags = new SQLiteParameter();
                    var imageRating = new SQLiteParameter();
                    var imageIncluded = new SQLiteParameter();
                    var imageHash = new SQLiteParameter();
                    var imageLastRefreshDate = new SQLiteParameter();

                    imageInsert.CommandText = "INSERT INTO images (id, name, tags, dependentTags, rating, included, hash, lastRefreshDate) VALUES (?, ?, ?, ?, ?, ?, ?, ?)";
                    imageInsert.Parameters.Add(imageId);
                    imageInsert.Parameters.Add(imageName);
                    imageInsert.Parameters.Add(imageTags);
                    imageInsert.Parameters.Add(imageDependentTags);
                    imageInsert.Parameters.Add(imageRating);
                    imageInsert.Parameters.Add(imageIncluded);
                    imageInsert.Parameters.Add(imageHash);
                    imageInsert.Parameters.Add(imageLastRefreshDate);

                    int newImageId = 0;

                    foreach (ImageItem image in config.Images)
                    {
                        string newImageName = Path.Combine(importDirectory, image.Name);
                        bool included = true;
                        if (!config.UseSubdirectories && Path.GetDirectoryName(image.Name).Length > 0)
                        {
                            included = false;
                        }

                        imageId.Value = newImageId;
                        imageName.Value = newImageName;

                        if (image.Tags == null)
                        {
                            imageTags.Value = "";
                        }
                        else
                        {
                            imageTags.Value = image.Tags.ToPipeDelimitedList();
                        }

                        image.RebuildDependentTags(config.DependencyList);

                        if (image.DependentTags == null)
                        {
                            imageDependentTags.Value = "";
                        }
                        else
                        {
                            imageDependentTags.Value = image.DependentTags.ToPipeDelimitedList();
                        }
                        
                        imageRating.Value = image.Rating;
                        imageIncluded.Value = included;
                        imageHash.Value = image.Hash;
                        if (image.LastRefreshDate == default(DateTime))
                        {
                            imageLastRefreshDate.Value = null;
                        }
                        else
                        {
                            imageLastRefreshDate.Value = image.LastRefreshDate;
                        }

                        imageInsert.ExecuteNonQuery();

                        newImageId++;
                    }

                    transaction.Commit();
                }
                finally
                {
                    if (imageInsert != null)
                    {
                        imageInsert.Dispose();
                    }
                }
            }
        }

        public static void PopulateDefaultConfig(SQLiteConnection connection, DirectorySet sourceDirectories)
        {
            using (SQLiteTransaction transaction = connection.BeginTransaction())
            {
                // Add default view list
                InsertDefaultViewList(connection);

                // Add source directory set
                ViewerDatabaseConfig.UpdateSourceDirectories(sourceDirectories, connection);

                // Add default config settings
                AddSettingsList(connection, DatabaseConfigAccess.ConfigDefaults);

                // Add default input mappings
                InputMapping.InsertDefaultsIntoMapping(connection, true);

                transaction.Commit();
            }
        }
        #endregion

        public static void RebuildDependentTags(SQLiteConnection connection, List<KeyValuePair<string, string>> newDependencies)
        {
            var currentTags = new Dictionary<int, List<string>>();

            SQLiteCommand getCurrentTags = new SQLiteCommand("SELECT id, tags FROM images", connection);
            using (SQLiteDataReader reader = getCurrentTags.ExecuteReader())
            {
                while (reader.Read())
                {
                    if (reader.IsDBNull("tags"))
                    {
                        currentTags.Add(reader.GetInt32("id"), new List<string>());
                    }
                    else
                    {
                        currentTags.Add(reader.GetInt32("id"), Utilities.ListFromPipeList(reader.GetString("tags")));
                    }
                }
            }

            using (SQLiteTransaction transaction = connection.BeginTransaction())
            {
                var depTagParameter = new SQLiteParameter();
                var idParameter = new SQLiteParameter();
                SQLiteCommand updateDepTags = new SQLiteCommand(connection);
                updateDepTags.CommandText = "UPDATE images SET dependentTags = ? WHERE id = ?";
                updateDepTags.Parameters.Add(depTagParameter);
                updateDepTags.Parameters.Add(idParameter);

                foreach (KeyValuePair<int, List<string>> pair in currentTags)
                {
                    if (pair.Value.Count > 0)
                    {
                        depTagParameter.Value = Utilities.CalculateDependentTags(pair.Value, newDependencies);
                        idParameter.Value = pair.Key;
                        updateDepTags.ExecuteNonQuery();
                    }
                }

                transaction.Commit();
            }
        }

        public static List<string> GetTags(SQLiteConnection connection, int imageId)
        {
            SQLiteCommand getTags = new SQLiteCommand("SELECT tags FROM images WHERE id = " + imageId, connection);
            using (SQLiteDataReader reader = getTags.ExecuteReader())
            {
                if (reader.Read() && !reader.IsDBNull("tags"))
                {
                    return Utilities.ListFromPipeList(reader.GetString("tags"));
                }
                else
                {
                    return new List<string>();
                }
            }
        }

        public static void SetTags(SQLiteConnection connection, List<string> tags, int imageId, List<KeyValuePair<string, string>> dependencyList)
        {
            SQLiteCommand updateTags = new SQLiteCommand("UPDATE images SET tags = '" + Utilities.PipeListFromList(tags) + "', dependentTags = '" + Utilities.CalculateDependentTags(tags, dependencyList) + "' WHERE id = " + imageId, connection);
            updateTags.ExecuteNonQuery();
        }

        public static void SetTags(SQLiteConnection connection, List<string> tags, int imageId, List<KeyValuePair<string, string>> dependencyList, List<string> oldTags)
        {
            foreach (string tag in oldTags)
            {
                DecrementTagSummary(tag);
            }

            foreach (string tag in tags)
            {
                IncrementTagSummary(tag);
            }

            SetTags(connection, tags, imageId, dependencyList);
        }

        public static void AddTagToImages(SQLiteConnection connection, string tag, List<int> images, List<KeyValuePair<string, string>> dependencyList)
        {
            // First get all the tags.
            Dictionary<int, List<string>> tagList = GetMultipleTags(connection, images);

            // Now, add the tag to all the tag collections
            foreach (List<string> tags in tagList.Values)
            {
                if (!tags.Contains(tag))
                {
                    IncrementTagSummary(tag);

                    tags.Add(tag);
                }
            }

            // Finally, write out the new batch of tags
            SetMultipleTags(connection, tagList, dependencyList);
        }

        public static void RemoveTagFromImages(SQLiteConnection connection, string tag, List<int> images, List<KeyValuePair<string, string>> dependencyList)
        {
            // First get all the tags.
            Dictionary<int, List<string>> tagList = GetMultipleTags(connection, images);

            // Now, add the tag to all the tag collections
            foreach (List<string> tags in tagList.Values)
            {
                if (tags.Contains(tag))
                {
                    DecrementTagSummary(tag);

                    tags.Remove(tag);
                }
            }

            // Finally, write out the new batch of tags
            SetMultipleTags(connection, tagList, dependencyList);
        }

        public static Dictionary<int, List<string>> GetMultipleTags(SQLiteConnection connection, List<int> images)
        {
            var result = new Dictionary<int, List<string>>();

            var idParameter = new SQLiteParameter();

            SQLiteCommand getTags = new SQLiteCommand(connection);
            getTags.CommandText = "SELECT tags FROM images WHERE id = ?";
            getTags.Parameters.Add(idParameter);

            foreach (int imageId in images)
            {
                idParameter.Value = imageId;
                using (SQLiteDataReader reader = getTags.ExecuteReader())
                {
                    if (reader.Read() && !reader.IsDBNull("tags"))
                    {
                        result.Add(imageId, Utilities.ListFromPipeList(reader.GetString("tags")));
                    }
                    else
                    {
                        result.Add(imageId, new List<string>());
                    }
                }
            }

            return result;
        }

        public static void SetMultipleTags(SQLiteConnection connection, Dictionary<int, List<string>> newTags, List<KeyValuePair<string, string>> dependencyList)
        {
            using (SQLiteTransaction transaction = connection.BeginTransaction())
            {
                var tagParameter = new SQLiteParameter();
                var depTagParameter = new SQLiteParameter();
                var idParameter = new SQLiteParameter();

                SQLiteCommand updateTags = new SQLiteCommand(connection);
                updateTags.CommandText = "UPDATE images SET tags = ?, dependentTags = ? WHERE id = ?";
                updateTags.Parameters.Add(tagParameter);
                updateTags.Parameters.Add(depTagParameter);
                updateTags.Parameters.Add(idParameter);

                foreach (KeyValuePair<int, List<string>> pair in newTags)
                {
                    tagParameter.Value = Utilities.PipeListFromList(pair.Value);
                    depTagParameter.Value = Utilities.CalculateDependentTags(pair.Value, dependencyList);
                    idParameter.Value = pair.Key;

                    updateTags.ExecuteNonQuery();
                }

                transaction.Commit();
            }
        }

        public static void RenameTag(string oldTag, string newTag, List<KeyValuePair<string, string>> dependencyList, SQLiteConnection connection)
        {
            using (SQLiteTransaction transaction = connection.BeginTransaction())
            {
                SQLiteCommand updateTags = new SQLiteCommand("UPDATE images SET tags = ?, dependentTags = ? WHERE id = ?", connection);
                SQLiteParameter updateTagsTagsParam = updateTags.Parameters.Add("tags", System.Data.DbType.String);
                SQLiteParameter updateTagsDependentTagsParam = updateTags.Parameters.Add("dependentTags", System.Data.DbType.String);
                SQLiteParameter updateTagsIdParam = updateTags.Parameters.Add("id", System.Data.DbType.Int32);

                SQLiteCommand getTags = new SQLiteCommand("SELECT tags, id FROM images WHERE tags LIKE '%|" + oldTag + "|%' AND included = 1", connection);
                using (SQLiteDataReader reader = getTags.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        if (!reader.IsDBNull("tags"))
                        {
                            List<string> tags = Utilities.ListFromPipeList(reader.GetString("tags"));

                            int oldTagLocation = tags.IndexOf(oldTag);
                            tags.RemoveAt(oldTagLocation);
                            tags.Insert(oldTagLocation, newTag);

                            updateTagsTagsParam.Value = Utilities.PipeListFromList(tags);
                            updateTagsDependentTagsParam.Value = Utilities.CalculateDependentTags(tags, dependencyList);
                            updateTagsIdParam.Value = reader.GetInt32("id");

                            updateTags.ExecuteNonQuery();
                        }
                    }
                }

                transaction.Commit();
            }
        }

        public static void DeleteTag(string tagToDelete, List<KeyValuePair<string, string>> dependencyList, SQLiteConnection connection)
        {
            using (SQLiteTransaction transaction = connection.BeginTransaction())
            {
                SQLiteCommand deleteTag = new SQLiteCommand("UPDATE images SET tags = ?, dependentTags = ? WHERE id = ?", connection);
                SQLiteParameter deleteTagTagsParam = deleteTag.Parameters.Add("tags", System.Data.DbType.String);
                SQLiteParameter deleteTagDependentTagsParam = deleteTag.Parameters.Add("dependentTags", System.Data.DbType.String);
                SQLiteParameter deleteTagIdParam = deleteTag.Parameters.Add("id", System.Data.DbType.Int32);

                SQLiteCommand getTags = new SQLiteCommand("SELECT tags, id FROM images WHERE tags LIKE '%|" + tagToDelete + "|%' AND included = 1", connection);
                using (SQLiteDataReader reader = getTags.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        if (!reader.IsDBNull("tags"))
                        {
                            List<string> tags = Utilities.ListFromPipeList(reader.GetString("tags"));

                            tags.Remove(tagToDelete);

                            deleteTagTagsParam.Value = Utilities.PipeListFromList(tags);
                            deleteTagDependentTagsParam.Value = Utilities.CalculateDependentTags(tags, dependencyList);
                            deleteTagIdParam.Value = reader.GetInt32("id");

                            deleteTag.ExecuteNonQuery();
                        }
                    }
                }

                transaction.Commit();
            }
        }

        public static void SetRating(SQLiteConnection connection, int rating, int imageId)
        {
            SQLiteCommand updateRating = new SQLiteCommand("UPDATE images SET rating = " + rating + " WHERE id = " + imageId, connection);
            updateRating.ExecuteNonQuery();
        }

        public static ImageUserData GetImageUserData(SQLiteConnection connection, int imageId)
        {
            SQLiteCommand getCommand = new SQLiteCommand("SELECT id, name, tags, rating FROM images WHERE id = " + imageId, connection);
            
            using (SQLiteDataReader reader = getCommand.ExecuteReader())
            {
                if (reader.Read())
                {
                    ImageUserData result = new ImageUserData();

                    result.Id = reader.GetInt32("id");
                    result.Name = reader.GetString("name");

                    if (!reader.IsDBNull("tags"))
                    {
                        result.Tags = Utilities.ListFromPipeList(reader.GetString("tags"));
                    }

                    if (!reader.IsDBNull("rating"))
                    {
                        result.Rating = reader.GetInt32("rating");
                    }

                    return result;
                }
            }

            return null;
        }

        public static string GetImageName(SQLiteConnection connection, int imageId)
        {
            SQLiteCommand getCommand = new SQLiteCommand("SELECT name FROM images WHERE id = " + imageId, connection);

            using (SQLiteDataReader reader = getCommand.ExecuteReader())
            {
                if (reader.Read())
                {
                    return reader.GetString("name");
                }
            }

            return null;
        }

        public static void SetImageName(SQLiteConnection connection, int imageId, string newName)
        {
            SQLiteCommand updateName = new SQLiteCommand("UPDATE images SET name = \"" + newName + "\" WHERE id = " + imageId, connection);
            updateName.ExecuteNonQuery();
        }

        public static void RemoveImageData(int imageToRemove, SQLiteConnection connection)
        {
            List<int> imagesToRemove = new List<int>();
            imagesToRemove.Add(imageToRemove);

            DecrementRemovedImages(imagesToRemove, connection);

            using (SQLiteTransaction transaction = connection.BeginTransaction())
            {
                SQLiteCommand deleteImage = new SQLiteCommand("DELETE FROM images WHERE id = " + imageToRemove, connection);
                deleteImage.ExecuteNonQuery();

                SQLiteCommand deleteImageSummary = new SQLiteCommand("DELETE FROM summaries WHERE imageId = " + imageToRemove, connection);
                deleteImageSummary.ExecuteNonQuery();

                transaction.Commit();
            }
        }

        public static void RemoveImageData(List<int> imagesToRemove, SQLiteConnection connection)
        {
            DecrementRemovedImages(imagesToRemove, connection);

            using (SQLiteTransaction transaction = connection.BeginTransaction())
            {
                var idParameter = new SQLiteParameter();
                SQLiteCommand removeImage = new SQLiteCommand(connection);
                removeImage.CommandText = "DELETE FROM images WHERE id = ?";
                removeImage.Parameters.Add(idParameter);

                var idSummaryParameter = new SQLiteParameter();
                SQLiteCommand removeImageSummary = new SQLiteCommand(connection);
                removeImageSummary.CommandText = "DELETE FROM summaries WHERE imageId = ?";
                removeImageSummary.Parameters.Add(idSummaryParameter);

                foreach (int imageId in imagesToRemove)
                {
                    idParameter.Value = imageId;
                    removeImage.ExecuteNonQuery();

                    idSummaryParameter.Value = imageId;
                    removeImageSummary.ExecuteNonQuery();
                }

                transaction.Commit();
            }
        }

        public static void RefreshIncludedFlag(SQLiteConnection connection, DirectorySet sourceDirectories)
        {
            using (SQLiteTransaction transaction = connection.BeginTransaction())
            {
                SQLiteCommand getState = new SQLiteCommand("SELECT id, name, included FROM images", connection);
                using (SQLiteDataReader reader = getState.ExecuteReader())
                {
                    var includedParam = new SQLiteParameter();
                    var idParam = new SQLiteParameter();
                    SQLiteCommand updateIncludedFlag = new SQLiteCommand(connection);
                    updateIncludedFlag.CommandText = "UPDATE images SET included = ? WHERE id = ?";
                    updateIncludedFlag.Parameters.Add(includedParam);
                    updateIncludedFlag.Parameters.Add(idParam);

                    while (reader.Read())
                    {
                        string fileDirectory = Path.GetDirectoryName(reader.GetString("name"));

                        bool oldIncluded = reader.GetBoolean("included");
                        bool newIncluded = sourceDirectories.PathIsIncluded(fileDirectory);

                        if (newIncluded != oldIncluded)
                        {
                            includedParam.Value = newIncluded;
                            idParam.Value = reader.GetInt32("id");
                            updateIncludedFlag.ExecuteNonQuery();
                        }
                    }
                }

                transaction.Commit();
            }
        }

        public static void ApplyOrphanedData(List<OrphanedData> orphanedImages, SQLiteConnection connection, List<KeyValuePair<string, string>> tagDependencies, bool useHashes)
        {
            // Find new location for orphaned data.
            if (orphanedImages.Count > 0)
            {
                SQLiteCommand getImageFromHash = new SQLiteCommand("SELECT id FROM images WHERE hash = ? AND included", connection);
                var hashParam = new SQLiteParameter();
                getImageFromHash.Parameters.Add(hashParam);

                foreach (OrphanedData orphanedData in orphanedImages)
                {
                    if (useHashes && !string.IsNullOrEmpty(orphanedData.Hash))
                    {
                        hashParam.Value = orphanedData.Hash;
                        using (SQLiteDataReader reader = getImageFromHash.ExecuteReader())
                        {
                            if (reader.Read())
                            {
                                orphanedData.ImageId = reader.GetInt32("id");
                                orphanedData.FoundOwner = true;
                            }
                        }
                    }
                    else
                    {
                        SQLiteCommand getImageFromName = new SQLiteCommand("SELECT id FROM images WHERE name LIKE \"%\\" + Path.GetFileName(orphanedData.Name) + "\" AND included", connection);
                        using (SQLiteDataReader reader = getImageFromName.ExecuteReader())
                        {
                            if (reader.Read())
                            {
                                orphanedData.ImageId = reader.GetInt32("id");
                                orphanedData.FoundOwner = true;
                            }
                        }
                    }
                }

                // Apply the orphaned data to the found matches

                using (SQLiteTransaction transaction = connection.BeginTransaction())
                {
                    var updateImageTags = new SQLiteParameter();
                    var updateImageDependentTags = new SQLiteParameter();
                    var updateImageRating = new SQLiteParameter();
                    var updateImageId = new SQLiteParameter();

                    SQLiteCommand updateImage = new SQLiteCommand(connection);
                    updateImage.CommandText = "UPDATE images SET tags = ?, dependentTags = ?, rating = ? WHERE id = ?";
                    updateImage.Parameters.Add(updateImageTags);
                    updateImage.Parameters.Add(updateImageDependentTags);
                    updateImage.Parameters.Add(updateImageRating);
                    updateImage.Parameters.Add(updateImageId);

                    foreach (OrphanedData orphanedData in orphanedImages)
                    {
                        if (orphanedData.FoundOwner)
                        {
                            updateImageTags.Value = orphanedData.Tags;
                            updateImageDependentTags.Value = Utilities.CalculateDependentTags(Utilities.ListFromPipeList(orphanedData.Tags), tagDependencies);
                            updateImageRating.Value = orphanedData.Rating;
                            updateImageId.Value = orphanedData.ImageId;

                            updateImage.ExecuteNonQuery();
                        }
                    }

                    transaction.Commit();
                }
            }
        }

        public static Dictionary<string, int> GetTagsSummary(SQLiteConnection connection)
        {
            if (tagsSummary == null)
            {
                tagsSummary = new Dictionary<string, int>();

                SQLiteCommand selectTags = new SQLiteCommand("SELECT tags FROM images WHERE included = 1", connection);
                using (SQLiteDataReader reader = selectTags.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        if (!reader.IsDBNull("tags"))
                        {
                            List<string> tags = Utilities.ListFromPipeList(reader.GetString("tags"));
                            foreach (string tag in tags)
                            {
                                IncrementTagSummary(tag);
                            }
                        }
                    }
                }
            }

            return tagsSummary;
        }

        public static void RebuildTagsSummary()
        {
            tagsSummary = null;
        }

        public static void IncrementTagSummary(string tag)
        {
            if (tagsSummary == null)
            {
                return;
            }

            if (!tagsSummary.ContainsKey(tag))
            {
                tagsSummary.Add(tag, 0);
            }

            tagsSummary[tag]++;
        }

        public static void DecrementTagSummary(string tag)
        {
            if (tagsSummary == null)
            {
                return;
            }

            if (tagsSummary.ContainsKey(tag))
            {
                tagsSummary[tag]--;

                if (tagsSummary[tag] == 0)
                {
                    tagsSummary.Remove(tag);
                }
            }
        }

        public static long GetLastInsertId(SQLiteConnection connection)
        {
            SQLiteCommand getLastIdCommand = new SQLiteCommand("SELECT max(id) AS lastInsertId FROM images", connection);
            object lastIdObject = getLastIdCommand.ExecuteScalar();

            try
            {
                return (long)lastIdObject;
            }
            catch (InvalidCastException)
            {
                return -1;
            }
        }

        public static void ExecuteNonQuery(string query, SQLiteConnection connection)
        {
            using (SQLiteCommand command = new SQLiteCommand(connection))
            {
                command.CommandText = query;
                command.ExecuteNonQuery();
            }
        }

        private static void DecrementRemovedImages(List<int> removedImages, SQLiteConnection connection)
        {
            if (tagsSummary != null)
            {
                SQLiteCommand getTagsFromImages = new SQLiteCommand("SELECT tags, included FROM images WHERE id = ?", connection);
                SQLiteParameter getTagsImageIdParam = getTagsFromImages.Parameters.Add("imageId", System.Data.DbType.Int32);

                foreach (int imageId in removedImages)
                {
                    getTagsImageIdParam.Value = imageId;
                    using (SQLiteDataReader reader = getTagsFromImages.ExecuteReader())
                    {
                        if (reader.Read())
                        {
                            if (!reader.IsDBNull("included") && reader.GetBoolean("included") && !reader.IsDBNull("tags"))
                            {
                                List<string> tags = Utilities.ListFromPipeList(reader.GetString("tags"));

                                foreach (string tag in tags)
                                {
                                    DecrementTagSummary(tag);
                                }
                            }
                        }
                    }
                }
            }
        }

        private static void InsertDefaultViewList(SQLiteConnection connection)
        {
            var insertDefaultList = new SQLiteCommand(connection);
            insertDefaultList.CommandText = "INSERT INTO viewLists (id, name, listIndex, orderBy, ascending, rating, searchString, searchTags, searchFileName, untagged, customClause) VALUES (1, 'Default', 0, 'random', ?, 0, '', ?, ?, ?, '')";

            var ascendingParam = new SQLiteParameter { Value = false };
            insertDefaultList.Parameters.Add(ascendingParam);

            var searchTagsParam = new SQLiteParameter { Value = false };
            insertDefaultList.Parameters.Add(searchTagsParam);

            var searchFileNameParam = new SQLiteParameter { Value = false };
            insertDefaultList.Parameters.Add(searchFileNameParam);

            var untaggedParam = new SQLiteParameter { Value = false };
            insertDefaultList.Parameters.Add(untaggedParam);

            insertDefaultList.ExecuteNonQuery();
        }

        private static void AddSettingsList(SQLiteConnection connection, Dictionary<string, string> settingsList)
        {
            using (SQLiteCommand settingsInsert = new SQLiteCommand(connection))
            {
                // Add settings
                var settingsName = new SQLiteParameter();
                var settingsValue = new SQLiteParameter();

                settingsInsert.CommandText = "INSERT INTO settings (name, value) VALUES (?, ?)";
                settingsInsert.Parameters.Add(settingsName);
                settingsInsert.Parameters.Add(settingsValue);

                foreach (KeyValuePair<string, string> pair in settingsList)
                {
                    settingsName.Value = pair.Key;
                    settingsValue.Value = pair.Value;

                    settingsInsert.ExecuteNonQuery();
                }
            }
        }
    }
}
